{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT Tutorial\n",
    "\n",
    "\n",
    "MLDB comes with a powerful implementation of [SQL's `SELECT` Queries](../../../../doc/#builtin/sql/Sql.md.html). This tutorial will walk you through the basics of `SELECT`, and some MLDB-specific features.\n",
    "\n",
    "The notebook cells below use `pymldb`'s `.query()` method; you can check out the [Using `pymldb` Tutorial](../../../../doc/nblink.html#_tutorials/Using pymldb Tutorial) for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pymldb import Connection\n",
    "mldb = Connection()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `SELECT`\n",
    "\n",
    "All queries start with the keyword `SELECT`. Here is the simplest possible query: we ask for 1 and we get a very short result set consisting of one row with one column named 1 and the single cell in it also contains 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          1\n",
       "_rowName   \n",
       "          1"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select 1\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course we can ask for more: the query below does a little math and shows how you can rename your columns with the `as` keyword. Note that single-quotes (`'`) are used to denote strings and double-quotes (`\"`) denote column names, both of which can contain any Unicode character."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1+1</th>\n",
       "      <th>var</th>\n",
       "      <th>hello, François</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>UTF8 striñg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          1+1  var hello, François\n",
       "_rowName                          \n",
       "            2    7     UTF8 striñg"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select 1+1, 3+4 as var, 'UTF8 striñg' as \"hello, François\"\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use a variety of operators in a `SELECT` expression, like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1 between 0 and 2</th>\n",
       "      <th>2 in (1,2,3)</th>\n",
       "      <th>3 is integer</th>\n",
       "      <th>(case when 4&lt;5 then 'yes' else 'no' end)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          1 between 0 and 2  2 in (1,2,3)  3 is integer  \\\n",
       "_rowName                                                  \n",
       "                          1             1             1   \n",
       "\n",
       "         (case when 4<5 then 'yes' else 'no' end)  \n",
       "_rowName                                           \n",
       "                                              yes  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select \n",
    "    1 between 0 and 2,\n",
    "    2 in (1,2,3),\n",
    "    3 is integer,\n",
    "    (case when 4<5 then 'yes' else 'no' end)\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `FROM` and `LIMIT`\n",
    "\n",
    "Queries are mostly useful when run on actual datasets, so let's import part of the passenger manifest from the Titanic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/import_titanic', { \n",
    "    \"type\": \"import.text\",\n",
    "    \"params\": { \n",
    "        \"dataFileUrl\": \"http://public.mldb.ai/titanic_train.csv\",\n",
    "        \"outputDataset\": \"titanic\",\n",
    "        \"runOnCreation\": True\n",
    "    } \n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's query all columns with the star (`*`) operator `FROM` our `titanic` dataset, using the `LIMIT` keyword to avoid getting too much output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>label</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>96</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>ShorneyMr.CharlesJoseph</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>374910</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>272</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>TornquistMr.WilliamHenry</td>\n",
       "      <td>male</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>LINE</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>523</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>LahoudMr.Sarkis</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2624</td>\n",
       "      <td>7.2250</td>\n",
       "      <td>None</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>277</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>LindblomMiss.AugustaCharlotta</td>\n",
       "      <td>female</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>347073</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>210</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>BlankMr.Henry</td>\n",
       "      <td>male</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>112277</td>\n",
       "      <td>31.0000</td>\n",
       "      <td>A31</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>209</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>CarrMiss.Helen\"Ellen\"</td>\n",
       "      <td>female</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>367231</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>None</td>\n",
       "      <td>Q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NasserMrs.Nicholas(AdeleAchem)</td>\n",
       "      <td>female</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>237736</td>\n",
       "      <td>30.0708</td>\n",
       "      <td>None</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>280</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>AbbottMrs.Stanton(RosaHunt)</td>\n",
       "      <td>female</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>C.A.2673</td>\n",
       "      <td>20.2500</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>697</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>KellyMr.James</td>\n",
       "      <td>male</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>363592</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>459</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>ToomeyMiss.Ellen</td>\n",
       "      <td>female</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>F.C.C.13531</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PassengerId  label  Pclass                            Name     Sex  \\\n",
       "_rowName                                                                       \n",
       "97                 96      0       3         ShorneyMr.CharlesJoseph    male   \n",
       "273               272      1       3        TornquistMr.WilliamHenry    male   \n",
       "524               523      0       3                 LahoudMr.Sarkis    male   \n",
       "278               277      0       3   LindblomMiss.AugustaCharlotta  female   \n",
       "211               210      1       1                   BlankMr.Henry    male   \n",
       "210               209      1       3           CarrMiss.Helen\"Ellen\"  female   \n",
       "11                 10      1       2  NasserMrs.Nicholas(AdeleAchem)  female   \n",
       "281               280      1       3     AbbottMrs.Stanton(RosaHunt)  female   \n",
       "698               697      0       3                   KellyMr.James    male   \n",
       "460               459      1       2                ToomeyMiss.Ellen  female   \n",
       "\n",
       "          Age  SibSp  Parch       Ticket     Fare Cabin Embarked  \n",
       "_rowName                                                          \n",
       "97        NaN      0      0       374910   8.0500  None        S  \n",
       "273        25      0      0         LINE   0.0000  None        S  \n",
       "524       NaN      0      0         2624   7.2250  None        C  \n",
       "278        45      0      0       347073   7.7500  None        S  \n",
       "211        40      0      0       112277  31.0000   A31        C  \n",
       "210        16      0      0       367231   7.7500  None        Q  \n",
       "11         14      1      0       237736  30.0708  None        C  \n",
       "281        35      1      1     C.A.2673  20.2500  None        S  \n",
       "698        44      0      0       363592   8.0500  None        S  \n",
       "460        50      0      0  F.C.C.13531  10.5000  None        S  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select *\n",
    "from titanic\n",
    "limit 10\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also ask for just certain columns by name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>ShorneyMr.CharlesJoseph</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>TornquistMr.WilliamHenry</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>LahoudMr.Sarkis</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>LindblomMiss.AugustaCharlotta</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>BlankMr.Henry</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>CarrMiss.Helen\"Ellen\"</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NasserMrs.Nicholas(AdeleAchem)</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>AbbottMrs.Stanton(RosaHunt)</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>KellyMr.James</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>ToomeyMiss.Ellen</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Name  Age\n",
       "_rowName                                     \n",
       "97               ShorneyMr.CharlesJoseph  NaN\n",
       "273             TornquistMr.WilliamHenry   25\n",
       "524                      LahoudMr.Sarkis  NaN\n",
       "278        LindblomMiss.AugustaCharlotta   45\n",
       "211                        BlankMr.Henry   40\n",
       "210                CarrMiss.Helen\"Ellen\"   16\n",
       "11        NasserMrs.Nicholas(AdeleAchem)   14\n",
       "281          AbbottMrs.Stanton(RosaHunt)   35\n",
       "698                        KellyMr.James   44\n",
       "460                     ToomeyMiss.Ellen   50"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Name, Age\n",
    "from titanic\n",
    "limit 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `ORDER BY`\n",
    "\n",
    "When we've used the `LIMIT` keyword above, we were just getting an arbitrary set of 10 rows. Using the `ORDER BY` keyword we can ask for the 'top 10' according to some criterion, for example `Age`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>BarkworthMr.AlgernonHenryWilson</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>853</th>\n",
       "      <td>SvenssonMr.Johan</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>GoldschmidtMr.GeorgeB</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>ArtagaveytiaMr.Ramon</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>ConnorsMr.Patrick</td>\n",
       "      <td>70.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>MitchellMr.HenryMichael</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>CrosbyCapt.EdwardGifford</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>WheadonMr.EdwardH</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>DuaneMr.Frank</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>MilletMr.FrancisDavis</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Name   Age\n",
       "_rowName                                       \n",
       "632       BarkworthMr.AlgernonHenryWilson  80.0\n",
       "853                      SvenssonMr.Johan  74.0\n",
       "98                  GoldschmidtMr.GeorgeB  71.0\n",
       "495                  ArtagaveytiaMr.Ramon  71.0\n",
       "118                     ConnorsMr.Patrick  70.5\n",
       "674               MitchellMr.HenryMichael  70.0\n",
       "747              CrosbyCapt.EdwardGifford  70.0\n",
       "35                      WheadonMr.EdwardH  66.0\n",
       "282                         DuaneMr.Frank  65.0\n",
       "458                 MilletMr.FrancisDavis  65.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Name, Age\n",
    "from titanic\n",
    "order by Age desc \n",
    "limit 10\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `WHERE`\n",
    "\n",
    "Beyond limiting the number of records, sometimes we want to look at records which match certain criteria, which we can do with the `WHERE` keyword. You can use the same operators in the `WHERE` clause as in the `SELECT` clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Sex</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>PeterMrs.Catherine(CatherineRizk)</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Name   Age  Pclass     Sex  SibSp  \\\n",
       "_rowName                                                                   \n",
       "535       PeterMrs.Catherine(CatherineRizk)  None       3  female      0   \n",
       "\n",
       "          Parch  label  \n",
       "_rowName                \n",
       "535           2      1  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Name, Age, Pclass, Sex, SibSp, Parch, label\n",
    "from titanic\n",
    "where Pclass in (1,3) and Sex='female' and (SibSp>3 or Parch=2) and label=1 and Age is null\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the query above we used the special operator `is` to retrieve only rows where `Age is null`. This is worth pointing out because `null` is a special value in SQL: it means \"unknown\". `null` has some strange properties, as you can see below: any comparison between `Age` and 1 returns `null`. This makes sense because if, say, `Age` is unknown, then we don't know if `Age` is less than, equal to or greater than anything else. SQL works according to [3-valued logic](https://en.wikipedia.org/wiki/Null_(SQL)).\n",
    "\n",
    "The only reliable way to check if a value is null is with the `is null` operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Age = 1</th>\n",
       "      <th>Age &lt; 1</th>\n",
       "      <th>Age &gt; 1</th>\n",
       "      <th>Age + 1</th>\n",
       "      <th>Age / 1</th>\n",
       "      <th>Age is null</th>\n",
       "      <th>Age is not null</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Age Age = 1 Age < 1 Age > 1 Age + 1 Age / 1  Age is null  \\\n",
       "_rowName                                                              \n",
       "97        None    None    None    None    None    None            1   \n",
       "\n",
       "          Age is not null  \n",
       "_rowName                   \n",
       "97                      0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Age, Age = 1, Age < 1, Age > 1, Age + 1, Age / 1, Age is null, Age is not null\n",
    "from titanic\n",
    "where Age is null\n",
    "limit 1\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Functions and Aggregate Functions\n",
    "\n",
    "MLDB comes with a number of builtin functions to operate on your data. Here's an example where we convert a string to uppercase and lowercase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>upper(Name)</th>\n",
       "      <th>lower(Name)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>BarkworthMr.AlgernonHenryWilson</td>\n",
       "      <td>BARKWORTHMR.ALGERNONHENRYWILSON</td>\n",
       "      <td>barkworthmr.algernonhenrywilson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>853</th>\n",
       "      <td>SvenssonMr.Johan</td>\n",
       "      <td>SVENSSONMR.JOHAN</td>\n",
       "      <td>svenssonmr.johan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>GoldschmidtMr.GeorgeB</td>\n",
       "      <td>GOLDSCHMIDTMR.GEORGEB</td>\n",
       "      <td>goldschmidtmr.georgeb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>ArtagaveytiaMr.Ramon</td>\n",
       "      <td>ARTAGAVEYTIAMR.RAMON</td>\n",
       "      <td>artagaveytiamr.ramon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>ConnorsMr.Patrick</td>\n",
       "      <td>CONNORSMR.PATRICK</td>\n",
       "      <td>connorsmr.patrick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>MitchellMr.HenryMichael</td>\n",
       "      <td>MITCHELLMR.HENRYMICHAEL</td>\n",
       "      <td>mitchellmr.henrymichael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>CrosbyCapt.EdwardGifford</td>\n",
       "      <td>CROSBYCAPT.EDWARDGIFFORD</td>\n",
       "      <td>crosbycapt.edwardgifford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>WheadonMr.EdwardH</td>\n",
       "      <td>WHEADONMR.EDWARDH</td>\n",
       "      <td>wheadonmr.edwardh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>DuaneMr.Frank</td>\n",
       "      <td>DUANEMR.FRANK</td>\n",
       "      <td>duanemr.frank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>MilletMr.FrancisDavis</td>\n",
       "      <td>MILLETMR.FRANCISDAVIS</td>\n",
       "      <td>milletmr.francisdavis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Name                      upper(Name)  \\\n",
       "_rowName                                                                     \n",
       "632       BarkworthMr.AlgernonHenryWilson  BARKWORTHMR.ALGERNONHENRYWILSON   \n",
       "853                      SvenssonMr.Johan                 SVENSSONMR.JOHAN   \n",
       "98                  GoldschmidtMr.GeorgeB            GOLDSCHMIDTMR.GEORGEB   \n",
       "495                  ArtagaveytiaMr.Ramon             ARTAGAVEYTIAMR.RAMON   \n",
       "118                     ConnorsMr.Patrick                CONNORSMR.PATRICK   \n",
       "674               MitchellMr.HenryMichael          MITCHELLMR.HENRYMICHAEL   \n",
       "747              CrosbyCapt.EdwardGifford         CROSBYCAPT.EDWARDGIFFORD   \n",
       "35                      WheadonMr.EdwardH                WHEADONMR.EDWARDH   \n",
       "282                         DuaneMr.Frank                    DUANEMR.FRANK   \n",
       "458                 MilletMr.FrancisDavis            MILLETMR.FRANCISDAVIS   \n",
       "\n",
       "                              lower(Name)  \n",
       "_rowName                                   \n",
       "632       barkworthmr.algernonhenrywilson  \n",
       "853                      svenssonmr.johan  \n",
       "98                  goldschmidtmr.georgeb  \n",
       "495                  artagaveytiamr.ramon  \n",
       "118                     connorsmr.patrick  \n",
       "674               mitchellmr.henrymichael  \n",
       "747              crosbycapt.edwardgifford  \n",
       "35                      wheadonmr.edwardh  \n",
       "282                         duanemr.frank  \n",
       "458                 milletmr.francisdavis  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Name, upper(Name), lower(Name)\n",
    "from titanic\n",
    "order by Age desc limit 10\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The functions below are special: they're aggregate functions, so they operate on multiple rows and give you a single output. They operate only on non-`null` values of their input."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(Age)</th>\n",
       "      <th>sum(Age)</th>\n",
       "      <th>sum(Age)/count(Age)</th>\n",
       "      <th>avg(Age)</th>\n",
       "      <th>min(Age)</th>\n",
       "      <th>max(Age)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[]</th>\n",
       "      <td>714</td>\n",
       "      <td>21205.17</td>\n",
       "      <td>29.699118</td>\n",
       "      <td>29.699118</td>\n",
       "      <td>0.42</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          count(Age)  sum(Age)  sum(Age)/count(Age)   avg(Age)  min(Age)  \\\n",
       "_rowName                                                                   \n",
       "[]               714  21205.17            29.699118  29.699118      0.42   \n",
       "\n",
       "          max(Age)  \n",
       "_rowName            \n",
       "[]              80  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select count(Age), sum(Age), sum(Age)/count(Age), avg(Age), min(Age), max(Age)\n",
    "from titanic\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `count` aggregate function is special in that it accepts `*` as an input, in which case it will return the count of all rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[]</th>\n",
       "      <td>891</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          count(*)\n",
       "_rowName          \n",
       "[]             891"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select count(*)\n",
    "from titanic\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GROUP BY & HAVING\n",
    "\n",
    " You can get aggregate functions to return multiple rows by grouping the input according to some criteria with the `GROUP BY` keyword. If you use an aggregate function in your `SELECT` clause, then you cannot use any non-aggregate expressions unless they appear in a `GROUP BY` clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pclass</th>\n",
       "      <th>avg(Age)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[1]</th>\n",
       "      <td>1</td>\n",
       "      <td>38.233441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[2]</th>\n",
       "      <td>2</td>\n",
       "      <td>29.877630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[3]</th>\n",
       "      <td>3</td>\n",
       "      <td>25.140620</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Pclass   avg(Age)\n",
       "_rowName                   \n",
       "[1]            1  38.233441\n",
       "[2]            2  29.877630\n",
       "[3]            3  25.140620"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Pclass, avg(Age)\n",
    "from titanic\n",
    "group by Pclass\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You cannot use aggregate functions in a `WHERE` clause. The `HAVING` clause is a little bit like a `WHERE` clause which is applied after `GROUP BY`, and in which you can use aggregate functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pclass</th>\n",
       "      <th>avg(Age)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[1]</th>\n",
       "      <td>1</td>\n",
       "      <td>38.233441</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Pclass   avg(Age)\n",
       "_rowName                   \n",
       "[1]            1  38.233441"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Pclass, avg(Age)\n",
    "from titanic\n",
    "group by Pclass\n",
    "having avg(Age) > 30\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced `FROM` with subqueries\n",
    "\n",
    "SQL allows you to use the output of one query as the input to another by putting queries in the `FROM` clause, at which point they become \"subqueries\". The following example shows how to emulate the `HAVING` example above with a subquery, although it should be noted that the `HAVING` form will be faster in this case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pclass</th>\n",
       "      <th>mean_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[1]</th>\n",
       "      <td>1</td>\n",
       "      <td>38.233441</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Pclass   mean_age\n",
       "_rowName                   \n",
       "[1]            1  38.233441"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select *\n",
    "from (\n",
    "    select Pclass, avg(Age) as mean_age\n",
    "    from titanic\n",
    "    group by Pclass\n",
    ")\n",
    "where mean_age > 30\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `INTO`: supported via `transform` Procedures \n",
    "\n",
    "Standard SQL defines an `INTO` clause to create new datasets from the output of queries. MLDB `SELECT` queries are idempotent (they do not modify anything) so `INTO` is not supported directly. You can accomplish the same task with a `transform` procedure, however:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "not_supported = \"\"\"\n",
    "    select Pclass, avg(Age) as mean_age\n",
    "    into class_stats\n",
    "    from titanic\n",
    "    group by Pclass\n",
    "\"\"\"\n",
    "\n",
    "supported = mldb.post('/v1/procedures', { \n",
    "    \"type\": \"transform\",\n",
    "    \"params\": { \n",
    "        \"inputData\": \"\"\"\n",
    "                select Pclass, avg(Age) as mean_age\n",
    "                from titanic\n",
    "                group by Pclass\n",
    "        \"\"\",\n",
    "        \"outputDataset\": \"class_stats\",\n",
    "        \"runOnCreation\": True\n",
    "    } \n",
    "})\n",
    "\n",
    "print supported"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now query our new dataset!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pclass</th>\n",
       "      <th>mean_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[1]</th>\n",
       "      <td>1</td>\n",
       "      <td>38.233441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[3]</th>\n",
       "      <td>3</td>\n",
       "      <td>25.140620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[2]</th>\n",
       "      <td>2</td>\n",
       "      <td>29.877630</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Pclass   mean_age\n",
       "_rowName                   \n",
       "[1]            1  38.233441\n",
       "[3]            3  25.140620\n",
       "[2]            2  29.877630"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select *\n",
    "from class_stats\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced `FROM` with `JOIN`\n",
    "\n",
    "You can run queries across multiple datasets with the `JOIN` keyword, using the `ON` keyword to define how to combine the datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>titanic.Name</th>\n",
       "      <th>titanic.Pclass</th>\n",
       "      <th>class_stats.Pclass</th>\n",
       "      <th>class_stats.mean_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[835]-[[3]]</th>\n",
       "      <td>AugustssonMr.Albert</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[60]-[[2]]</th>\n",
       "      <td>WestMiss.ConstanceMirium</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>29.87763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[78]-[[3]]</th>\n",
       "      <td>StaneffMr.Ivan</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[356]-[[3]]</th>\n",
       "      <td>YousifMr.Wazli</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[134]-[[3]]</th>\n",
       "      <td>RobinsMrs.AlexanderA(GraceCharityLaury)</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[740]-[[3]]</th>\n",
       "      <td>IvanoffMr.Kanio</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[652]-[[3]]</th>\n",
       "      <td>MitkoffMr.Mito</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[10]-[[3]]</th>\n",
       "      <td>JohnsonMrs.OscarW(ElisabethVilhelminaBerg)</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[249]-[[2]]</th>\n",
       "      <td>HamalainenMrs.William(Anna)</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>29.87763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[280]-[[3]]</th>\n",
       "      <td>RiceMaster.Eric</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>25.14062</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           titanic.Name  titanic.Pclass  \\\n",
       "_rowName                                                                  \n",
       "[835]-[[3]]                         AugustssonMr.Albert               3   \n",
       "[60]-[[2]]                     WestMiss.ConstanceMirium               2   \n",
       "[78]-[[3]]                               StaneffMr.Ivan               3   \n",
       "[356]-[[3]]                              YousifMr.Wazli               3   \n",
       "[134]-[[3]]     RobinsMrs.AlexanderA(GraceCharityLaury)               3   \n",
       "[740]-[[3]]                             IvanoffMr.Kanio               3   \n",
       "[652]-[[3]]                              MitkoffMr.Mito               3   \n",
       "[10]-[[3]]   JohnsonMrs.OscarW(ElisabethVilhelminaBerg)               3   \n",
       "[249]-[[2]]                 HamalainenMrs.William(Anna)               2   \n",
       "[280]-[[3]]                             RiceMaster.Eric               3   \n",
       "\n",
       "             class_stats.Pclass  class_stats.mean_age  \n",
       "_rowName                                               \n",
       "[835]-[[3]]                   3              25.14062  \n",
       "[60]-[[2]]                    2              29.87763  \n",
       "[78]-[[3]]                    3              25.14062  \n",
       "[356]-[[3]]                   3              25.14062  \n",
       "[134]-[[3]]                   3              25.14062  \n",
       "[740]-[[3]]                   3              25.14062  \n",
       "[652]-[[3]]                   3              25.14062  \n",
       "[10]-[[3]]                    3              25.14062  \n",
       "[249]-[[2]]                   2              29.87763  \n",
       "[280]-[[3]]                   3              25.14062  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select titanic.Name, titanic.Pclass, class_stats.*\n",
    "from titanic JOIN class_stats ON titanic.Pclass = class_stats.Pclass    \n",
    "order by Age desc limit 10\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MLDB extensions to conventional SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB has some notable differences with more conventional SQL databases like PostgreSQL, MySQL, Oracle or SQLServer. For example, MLDB datasets are not SQL tables: \n",
    "\n",
    "* datasets have no fixed schema\n",
    "* datasets can have a variable number of columns, numbering into the millions\n",
    "* columns can contain mixed types (i.e. both numbers and strings in the same column)\n",
    "* both rows and columns have names\n",
    "\n",
    "In order to accomodate this, MLDB provides a number of extensions to standard SQL. Examples are provided below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting columns based on a prefix:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Parch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>96</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PassengerId  Pclass  Parch\n",
       "_rowName                            \n",
       "97                 96       3      0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select P*\n",
    "from titanic\n",
    "limit 1\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Renaming columns based on a prefix pattern:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>xassengerId</th>\n",
       "      <th>xclass</th>\n",
       "      <th>xarch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>96</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          xassengerId  xclass  xarch\n",
       "_rowName                            \n",
       "97                 96       3      0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select P* as x*\n",
    "from titanic\n",
    "limit 1\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excluding columns from a selection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>label</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>0</td>\n",
       "      <td>ShorneyMr.CharlesJoseph</td>\n",
       "      <td>male</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>374910</td>\n",
       "      <td>8.05</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          label                     Name   Sex   Age  SibSp  Ticket  Fare  \\\n",
       "_rowName                                                                    \n",
       "97            0  ShorneyMr.CharlesJoseph  male  None      0  374910  8.05   \n",
       "\n",
       "         Cabin Embarked  \n",
       "_rowName                 \n",
       "97        None        S  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select * excluding(P*)\n",
    "from titanic\n",
    "limit 1\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NOTE: Selecting a column which is not in the dataset will not cause an error, instead it will return `NULL`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nothing</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         nothing\n",
       "_rowName        \n",
       "97          None"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select nothing\n",
    "from titanic\n",
    "limit 1\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB supports JSON-like objects in queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>{a: 1, b:2, c: 'hello'}.a</th>\n",
       "      <th>{a: 1, b:2, c: 'hello'}.b</th>\n",
       "      <th>{a: 1, b:2, c: 'hello'}.c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          {a: 1, b:2, c: 'hello'}.a  {a: 1, b:2, c: 'hello'}.b  \\\n",
       "_rowName                                                         \n",
       "                                  1                          2   \n",
       "\n",
       "         {a: 1, b:2, c: 'hello'}.c  \n",
       "_rowName                            \n",
       "                             hello  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select {a: 1, b:2, c: 'hello'}\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>obj.a</th>\n",
       "      <th>obj.b</th>\n",
       "      <th>obj.c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          obj.a  obj.b  obj.c\n",
       "_rowName                     \n",
       "              1      2  hello"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select {a: 1, b:2, c: 'hello'} as obj\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b.x</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a  b.x      c\n",
       "_rowName               \n",
       "          1    2  hello"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select {a: 1, b:{x:2}, c: 'hello'} as *\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is especially useful for tokenizing text into bags of words, or importing semi-structured JSON data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>world</th>\n",
       "      <th>Hello</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          world  Hello\n",
       "_rowName              \n",
       "              1      2"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select tokenize('Hello world, Hello!', {splitChars: ' ,!'}) as *\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hello</th>\n",
       "      <th>list.0</th>\n",
       "      <th>list.1</th>\n",
       "      <th>list.2</th>\n",
       "      <th>list.3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>world</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          hello  list.0  list.1  list.2  list.3\n",
       "_rowName                                       \n",
       "          world       1       2       3       4"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select parse_json('{\"hello\":\"world\",\"list\":[1,2,3,4]}') as *\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB's object notation also allows you to run aggregate functions on multiple columns at once, with the special `{*}` notation, which refers to all fields in the current row as an object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pclass</th>\n",
       "      <th>count(*)</th>\n",
       "      <th>count({*}).Age</th>\n",
       "      <th>count({*}).Cabin</th>\n",
       "      <th>count({*}).Embarked</th>\n",
       "      <th>count({*}).Fare</th>\n",
       "      <th>count({*}).Name</th>\n",
       "      <th>count({*}).Parch</th>\n",
       "      <th>count({*}).PassengerId</th>\n",
       "      <th>count({*}).Pclass</th>\n",
       "      <th>count({*}).Sex</th>\n",
       "      <th>count({*}).SibSp</th>\n",
       "      <th>count({*}).Ticket</th>\n",
       "      <th>count({*}).label</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[1]</th>\n",
       "      <td>1</td>\n",
       "      <td>216</td>\n",
       "      <td>186</td>\n",
       "      <td>176</td>\n",
       "      <td>214</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[2]</th>\n",
       "      <td>2</td>\n",
       "      <td>184</td>\n",
       "      <td>173</td>\n",
       "      <td>16</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[3]</th>\n",
       "      <td>3</td>\n",
       "      <td>491</td>\n",
       "      <td>355</td>\n",
       "      <td>12</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Pclass  count(*)  count({*}).Age  count({*}).Cabin  \\\n",
       "_rowName                                                       \n",
       "[1]            1       216             186               176   \n",
       "[2]            2       184             173                16   \n",
       "[3]            3       491             355                12   \n",
       "\n",
       "          count({*}).Embarked  count({*}).Fare  count({*}).Name  \\\n",
       "_rowName                                                          \n",
       "[1]                       214              216              216   \n",
       "[2]                       184              184              184   \n",
       "[3]                       491              491              491   \n",
       "\n",
       "          count({*}).Parch  count({*}).PassengerId  count({*}).Pclass  \\\n",
       "_rowName                                                                \n",
       "[1]                    216                     216                216   \n",
       "[2]                    184                     184                184   \n",
       "[3]                    491                     491                491   \n",
       "\n",
       "          count({*}).Sex  count({*}).SibSp  count({*}).Ticket  \\\n",
       "_rowName                                                        \n",
       "[1]                  216               216                216   \n",
       "[2]                  184               184                184   \n",
       "[3]                  491               491                491   \n",
       "\n",
       "          count({*}).label  \n",
       "_rowName                    \n",
       "[1]                    216  \n",
       "[2]                    184  \n",
       "[3]                    491  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Pclass, count(*), count({*})\n",
    "from titanic\n",
    "group by Pclass\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB's flexible output model also enables powerful aggregate functions like `pivot()` to operate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Pclass</th>\n",
       "      <th>female</th>\n",
       "      <th>male</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[1]</th>\n",
       "      <td>1</td>\n",
       "      <td>94</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[2]</th>\n",
       "      <td>2</td>\n",
       "      <td>76</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[3]</th>\n",
       "      <td>3</td>\n",
       "      <td>144</td>\n",
       "      <td>347</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Pclass  female  male\n",
       "_rowName                      \n",
       "[1]            1      94   122\n",
       "[2]            2      76   108\n",
       "[3]            3     144   347"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Pclass, pivot(Sex, \"count(*)\") as *\n",
    "from (\n",
    "    select Pclass, Sex, count(*) from titanic group by Pclass, Sex\n",
    ")\n",
    "group by Pclass\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB supports multi-dimensional arrays called embeddings, also known as tensors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>embedding.0</th>\n",
       "      <th>embedding.1</th>\n",
       "      <th>embedding.2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          embedding.0  embedding.1  embedding.2\n",
       "_rowName                                       \n",
       "                    1            2            3"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select [1,2,3] as embedding\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>n.0</th>\n",
       "      <th>n.1</th>\n",
       "      <th>n.2</th>\n",
       "      <th>d.0</th>\n",
       "      <th>d.1</th>\n",
       "      <th>d.2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               n.0       n.1  n.2       d.0       d.1  d.2\n",
       "_rowName                                                  \n",
       "          0.166667  0.333333  0.5  0.166667  0.333333  0.5"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select normalize([1,2,3], 1) as n,  [1,2,3] / norm([1,2,3] ,1) as d\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB datasets have named rows as well as columns, and the `NAMED` keyword allows you to control the names of your output rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>label</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BarkworthMr.AlgernonHenryWilson aged 80</th>\n",
       "      <td>631</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>BarkworthMr.AlgernonHenryWilson</td>\n",
       "      <td>male</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27042</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>A23</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SvenssonMr.Johan aged 74</th>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>SvenssonMr.Johan</td>\n",
       "      <td>male</td>\n",
       "      <td>74.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>347060</td>\n",
       "      <td>7.7750</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GoldschmidtMr.GeorgeB aged 71</th>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>GoldschmidtMr.GeorgeB</td>\n",
       "      <td>male</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PC17754</td>\n",
       "      <td>34.6542</td>\n",
       "      <td>A5</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ArtagaveytiaMr.Ramon aged 71</th>\n",
       "      <td>494</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>ArtagaveytiaMr.Ramon</td>\n",
       "      <td>male</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PC17609</td>\n",
       "      <td>49.5042</td>\n",
       "      <td>None</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ConnorsMr.Patrick aged 70.5</th>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>ConnorsMr.Patrick</td>\n",
       "      <td>male</td>\n",
       "      <td>70.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>370369</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>None</td>\n",
       "      <td>Q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MitchellMr.HenryMichael aged 70</th>\n",
       "      <td>673</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>MitchellMr.HenryMichael</td>\n",
       "      <td>male</td>\n",
       "      <td>70.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>C.A.24580</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CrosbyCapt.EdwardGifford aged 70</th>\n",
       "      <td>746</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CrosbyCapt.EdwardGifford</td>\n",
       "      <td>male</td>\n",
       "      <td>70.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>WE/P5735</td>\n",
       "      <td>71.0000</td>\n",
       "      <td>B22</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WheadonMr.EdwardH aged 66</th>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>WheadonMr.EdwardH</td>\n",
       "      <td>male</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>C.A.24579</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>None</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DuaneMr.Frank aged 65</th>\n",
       "      <td>281</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>DuaneMr.Frank</td>\n",
       "      <td>male</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>336439</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>None</td>\n",
       "      <td>Q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MilletMr.FrancisDavis aged 65</th>\n",
       "      <td>457</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>MilletMr.FrancisDavis</td>\n",
       "      <td>male</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13509</td>\n",
       "      <td>26.5500</td>\n",
       "      <td>E38</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         PassengerId  label  Pclass  \\\n",
       "_rowName                                                              \n",
       "BarkworthMr.AlgernonHenryWilson aged 80          631      1       1   \n",
       "SvenssonMr.Johan aged 74                         852      0       3   \n",
       "GoldschmidtMr.GeorgeB aged 71                     97      0       1   \n",
       "ArtagaveytiaMr.Ramon aged 71                     494      0       1   \n",
       "ConnorsMr.Patrick aged 70.5                      117      0       3   \n",
       "MitchellMr.HenryMichael aged 70                  673      0       2   \n",
       "CrosbyCapt.EdwardGifford aged 70                 746      0       1   \n",
       "WheadonMr.EdwardH aged 66                         34      0       2   \n",
       "DuaneMr.Frank aged 65                            281      0       3   \n",
       "MilletMr.FrancisDavis aged 65                    457      0       1   \n",
       "\n",
       "                                                                    Name  \\\n",
       "_rowName                                                                   \n",
       "BarkworthMr.AlgernonHenryWilson aged 80  BarkworthMr.AlgernonHenryWilson   \n",
       "SvenssonMr.Johan aged 74                                SvenssonMr.Johan   \n",
       "GoldschmidtMr.GeorgeB aged 71                      GoldschmidtMr.GeorgeB   \n",
       "ArtagaveytiaMr.Ramon aged 71                        ArtagaveytiaMr.Ramon   \n",
       "ConnorsMr.Patrick aged 70.5                            ConnorsMr.Patrick   \n",
       "MitchellMr.HenryMichael aged 70                  MitchellMr.HenryMichael   \n",
       "CrosbyCapt.EdwardGifford aged 70                CrosbyCapt.EdwardGifford   \n",
       "WheadonMr.EdwardH aged 66                              WheadonMr.EdwardH   \n",
       "DuaneMr.Frank aged 65                                      DuaneMr.Frank   \n",
       "MilletMr.FrancisDavis aged 65                      MilletMr.FrancisDavis   \n",
       "\n",
       "                                          Sex   Age  SibSp  Parch     Ticket  \\\n",
       "_rowName                                                                       \n",
       "BarkworthMr.AlgernonHenryWilson aged 80  male  80.0      0      0      27042   \n",
       "SvenssonMr.Johan aged 74                 male  74.0      0      0     347060   \n",
       "GoldschmidtMr.GeorgeB aged 71            male  71.0      0      0    PC17754   \n",
       "ArtagaveytiaMr.Ramon aged 71             male  71.0      0      0    PC17609   \n",
       "ConnorsMr.Patrick aged 70.5              male  70.5      0      0     370369   \n",
       "MitchellMr.HenryMichael aged 70          male  70.0      0      0  C.A.24580   \n",
       "CrosbyCapt.EdwardGifford aged 70         male  70.0      1      1   WE/P5735   \n",
       "WheadonMr.EdwardH aged 66                male  66.0      0      0  C.A.24579   \n",
       "DuaneMr.Frank aged 65                    male  65.0      0      0     336439   \n",
       "MilletMr.FrancisDavis aged 65            male  65.0      0      0      13509   \n",
       "\n",
       "                                            Fare Cabin Embarked  \n",
       "_rowName                                                         \n",
       "BarkworthMr.AlgernonHenryWilson aged 80  30.0000   A23        S  \n",
       "SvenssonMr.Johan aged 74                  7.7750  None        S  \n",
       "GoldschmidtMr.GeorgeB aged 71            34.6542    A5        C  \n",
       "ArtagaveytiaMr.Ramon aged 71             49.5042  None        C  \n",
       "ConnorsMr.Patrick aged 70.5               7.7500  None        Q  \n",
       "MitchellMr.HenryMichael aged 70          10.5000  None        S  \n",
       "CrosbyCapt.EdwardGifford aged 70         71.0000   B22        S  \n",
       "WheadonMr.EdwardH aged 66                10.5000  None        S  \n",
       "DuaneMr.Frank aged 65                     7.7500  None        Q  \n",
       "MilletMr.FrancisDavis aged 65            26.5500   E38        S  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select *\n",
    "named Name + ' aged ' + cast(Age as string)\n",
    "from titanic\n",
    "order by Age desc limit 10\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Having named rows as well as columns allows us to easily operate on the transpose of a dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>632</th>\n",
       "      <th>853</th>\n",
       "      <th>98</th>\n",
       "      <th>495</th>\n",
       "      <th>118</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cabin</th>\n",
       "      <td>A23</td>\n",
       "      <td>None</td>\n",
       "      <td>A5</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fare</th>\n",
       "      <td>30</td>\n",
       "      <td>7.775</td>\n",
       "      <td>34.6542</td>\n",
       "      <td>49.5042</td>\n",
       "      <td>7.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SibSp</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ticket</th>\n",
       "      <td>27042</td>\n",
       "      <td>347060</td>\n",
       "      <td>PC17754</td>\n",
       "      <td>PC17609</td>\n",
       "      <td>370369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PassengerId</th>\n",
       "      <td>631</td>\n",
       "      <td>852</td>\n",
       "      <td>97</td>\n",
       "      <td>494</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Age</th>\n",
       "      <td>80</td>\n",
       "      <td>74</td>\n",
       "      <td>71</td>\n",
       "      <td>71</td>\n",
       "      <td>70.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pclass</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <td>BarkworthMr.AlgernonHenryWilson</td>\n",
       "      <td>SvenssonMr.Johan</td>\n",
       "      <td>GoldschmidtMr.GeorgeB</td>\n",
       "      <td>ArtagaveytiaMr.Ramon</td>\n",
       "      <td>ConnorsMr.Patrick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sex</th>\n",
       "      <td>male</td>\n",
       "      <td>male</td>\n",
       "      <td>male</td>\n",
       "      <td>male</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Parch</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Embarked</th>\n",
       "      <td>S</td>\n",
       "      <td>S</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>Q</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         632               853  \\\n",
       "_rowName                                                         \n",
       "Cabin                                    A23              None   \n",
       "Fare                                      30             7.775   \n",
       "SibSp                                      0                 0   \n",
       "Ticket                                 27042            347060   \n",
       "PassengerId                              631               852   \n",
       "label                                      1                 0   \n",
       "Age                                       80                74   \n",
       "Pclass                                     1                 3   \n",
       "Name         BarkworthMr.AlgernonHenryWilson  SvenssonMr.Johan   \n",
       "Sex                                     male              male   \n",
       "Parch                                      0                 0   \n",
       "Embarked                                   S                 S   \n",
       "\n",
       "                                98                   495                118  \n",
       "_rowName                                                                     \n",
       "Cabin                           A5                  None               None  \n",
       "Fare                       34.6542               49.5042               7.75  \n",
       "SibSp                            0                     0                  0  \n",
       "Ticket                     PC17754               PC17609             370369  \n",
       "PassengerId                     97                   494                117  \n",
       "label                            0                     0                  0  \n",
       "Age                             71                    71               70.5  \n",
       "Pclass                           1                     1                  3  \n",
       "Name         GoldschmidtMr.GeorgeB  ArtagaveytiaMr.Ramon  ConnorsMr.Patrick  \n",
       "Sex                           male                  male               male  \n",
       "Parch                            0                     0                  0  \n",
       "Embarked                         C                     C                  Q  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select * from transpose(\n",
    "    (select * from titanic order by Age desc limit 5)\n",
    ")\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB supports inline Javascript application via the `jseval()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>processed_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>BarkworthMr.AlgernonHenryWilson</td>\n",
       "      <td>Barkworth Mr. Algernon Henry Wilson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>853</th>\n",
       "      <td>SvenssonMr.Johan</td>\n",
       "      <td>Svensson Mr. Johan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>GoldschmidtMr.GeorgeB</td>\n",
       "      <td>Goldschmidt Mr. George B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>ArtagaveytiaMr.Ramon</td>\n",
       "      <td>Artagaveytia Mr. Ramon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>ConnorsMr.Patrick</td>\n",
       "      <td>Connors Mr. Patrick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>MitchellMr.HenryMichael</td>\n",
       "      <td>Mitchell Mr. Henry Michael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>CrosbyCapt.EdwardGifford</td>\n",
       "      <td>Crosby Capt. Edward Gifford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>WheadonMr.EdwardH</td>\n",
       "      <td>Wheadon Mr. Edward H</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>DuaneMr.Frank</td>\n",
       "      <td>Duane Mr. Frank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>MilletMr.FrancisDavis</td>\n",
       "      <td>Millet Mr. Francis Davis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Name  \\\n",
       "_rowName                                    \n",
       "632       BarkworthMr.AlgernonHenryWilson   \n",
       "853                      SvenssonMr.Johan   \n",
       "98                  GoldschmidtMr.GeorgeB   \n",
       "495                  ArtagaveytiaMr.Ramon   \n",
       "118                     ConnorsMr.Patrick   \n",
       "674               MitchellMr.HenryMichael   \n",
       "747              CrosbyCapt.EdwardGifford   \n",
       "35                      WheadonMr.EdwardH   \n",
       "282                         DuaneMr.Frank   \n",
       "458                 MilletMr.FrancisDavis   \n",
       "\n",
       "                                processed_name  \n",
       "_rowName                                        \n",
       "632        Barkworth Mr. Algernon Henry Wilson  \n",
       "853                         Svensson Mr. Johan  \n",
       "98                    Goldschmidt Mr. George B  \n",
       "495                     Artagaveytia Mr. Ramon  \n",
       "118                        Connors Mr. Patrick  \n",
       "674                 Mitchell Mr. Henry Michael  \n",
       "747                Crosby Capt. Edward Gifford  \n",
       "35                        Wheadon Mr. Edward H  \n",
       "282                            Duane Mr. Frank  \n",
       "458                   Millet Mr. Francis Davis  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select Name,\n",
    "    jseval(\n",
    "        'return Name.replace(/([A-Z])/g, function(m, p) { return \" \"+p; });',\n",
    "        'Name', Name\n",
    "    ) as processed_name\n",
    "from titanic\n",
    "order by Age desc limit 10\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MLDB datasets handle millions of columns, and deal very well with sparse datasets, making them ideal for operating on bags of words."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Henry</th>\n",
       "      <th>Algernon</th>\n",
       "      <th>Mr</th>\n",
       "      <th>Wilson</th>\n",
       "      <th>Barkworth</th>\n",
       "      <th>Johan</th>\n",
       "      <th>Svensson</th>\n",
       "      <th>B</th>\n",
       "      <th>George</th>\n",
       "      <th>Goldschmidt</th>\n",
       "      <th>...</th>\n",
       "      <th>Edward</th>\n",
       "      <th>Capt</th>\n",
       "      <th>Crosby</th>\n",
       "      <th>H</th>\n",
       "      <th>Wheadon</th>\n",
       "      <th>Frank</th>\n",
       "      <th>Duane</th>\n",
       "      <th>Davis</th>\n",
       "      <th>Francis</th>\n",
       "      <th>Millet</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>853</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Henry  Algernon  Mr  Wilson  Barkworth  Johan  Svensson   B  George  \\\n",
       "_rowName                                                                        \n",
       "632           1         1   1       1          1    NaN       NaN NaN     NaN   \n",
       "853         NaN       NaN   1     NaN        NaN      1         1 NaN     NaN   \n",
       "98          NaN       NaN   1     NaN        NaN    NaN       NaN   1       1   \n",
       "495         NaN       NaN   1     NaN        NaN    NaN       NaN NaN     NaN   \n",
       "118         NaN       NaN   1     NaN        NaN    NaN       NaN NaN     NaN   \n",
       "674           1       NaN   1     NaN        NaN    NaN       NaN NaN     NaN   \n",
       "747         NaN       NaN NaN     NaN        NaN    NaN       NaN NaN     NaN   \n",
       "35          NaN       NaN   1     NaN        NaN    NaN       NaN NaN     NaN   \n",
       "282         NaN       NaN   1     NaN        NaN    NaN       NaN NaN     NaN   \n",
       "458         NaN       NaN   1     NaN        NaN    NaN       NaN NaN     NaN   \n",
       "\n",
       "          Goldschmidt   ...    Edward  Capt  Crosby   H  Wheadon  Frank  \\\n",
       "_rowName                ...                                               \n",
       "632               NaN   ...       NaN   NaN     NaN NaN      NaN    NaN   \n",
       "853               NaN   ...       NaN   NaN     NaN NaN      NaN    NaN   \n",
       "98                  1   ...       NaN   NaN     NaN NaN      NaN    NaN   \n",
       "495               NaN   ...       NaN   NaN     NaN NaN      NaN    NaN   \n",
       "118               NaN   ...       NaN   NaN     NaN NaN      NaN    NaN   \n",
       "674               NaN   ...       NaN   NaN     NaN NaN      NaN    NaN   \n",
       "747               NaN   ...         1     1       1 NaN      NaN    NaN   \n",
       "35                NaN   ...         1   NaN     NaN   1        1    NaN   \n",
       "282               NaN   ...       NaN   NaN     NaN NaN      NaN      1   \n",
       "458               NaN   ...       NaN   NaN     NaN NaN      NaN    NaN   \n",
       "\n",
       "          Duane  Davis  Francis  Millet  \n",
       "_rowName                                 \n",
       "632         NaN    NaN      NaN     NaN  \n",
       "853         NaN    NaN      NaN     NaN  \n",
       "98          NaN    NaN      NaN     NaN  \n",
       "495         NaN    NaN      NaN     NaN  \n",
       "118         NaN    NaN      NaN     NaN  \n",
       "674         NaN    NaN      NaN     NaN  \n",
       "747         NaN    NaN      NaN     NaN  \n",
       "35          NaN    NaN      NaN     NaN  \n",
       "282           1    NaN      NaN     NaN  \n",
       "458         NaN      1        1       1  \n",
       "\n",
       "[10 rows x 27 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select tokenize(\n",
    "    jseval('\n",
    "        return Name.replace(/([A-Z])/g, function(m, p) { return \" \"+p; });\n",
    "    ', 'Name', Name),\n",
    "    {splitChars: ' .()\"', quoteChar:''}) as *\n",
    "from titanic\n",
    "order by Age desc\n",
    "limit 10\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Putting it all together, here are the top 20 tokens present in the names of Titanic passengers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mr</th>\n",
       "      <td>521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Miss</th>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mrs</th>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>William</th>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John</th>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Master</th>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Henry</th>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>George</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>James</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Charles</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas</th>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mary</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Edward</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Anna</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Joseph</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frederick</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johan</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Elizabeth</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Richard</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Samuel</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           counts\n",
       "_rowName         \n",
       "Mr            521\n",
       "Miss          182\n",
       "Mrs           128\n",
       "William        64\n",
       "John           44\n",
       "Master         40\n",
       "Henry          35\n",
       "George         24\n",
       "James          24\n",
       "Charles        24\n",
       "Thomas         22\n",
       "Mary           20\n",
       "Edward         18\n",
       "Anna           17\n",
       "Joseph         16\n",
       "Frederick      15\n",
       "Johan          15\n",
       "Elizabeth      15\n",
       "Richard        14\n",
       "Samuel         13"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "\n",
    "select * from transpose((\n",
    "    select sum(\n",
    "        tokenize(\n",
    "            jseval(\n",
    "                'return Name.replace(/([A-Z])/g, function(m, p) { return \" \"+p; });', \n",
    "                'Name', Name\n",
    "            ),\n",
    "            {splitChars: ' .()\"', quoteChar:''}\n",
    "        )\n",
    "    ) as *\n",
    "    named 'counts'\n",
    "    from titanic\n",
    "))\n",
    "order by counts desc limit 20\n",
    "\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to next?\n",
    "\n",
    "Check out the other [Tutorials and Demos](../../../../doc/#builtin/Demos.md.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
